﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CMS_GYM
{

    public class DINH_KHOAN_CO
    {
        public int TAI_KHOAN_CO { get; set; }
        public string TEN_TAI_KHOAN_CO { get; set; }
        public string GHI_CHU { get; set; }

        /// <summary>
        /// Doanh thu
        /// Chi phi
        /// </summary>
        public string LOAI_TAI_KHOAN { get; set; }
        public bool NHOM_TAI_KHOAN { get; set; }
        public bool MAC_DINH { get; set; }

        /// <summary>
        /// 1: Dang duoc su dung
        /// 0: Khong con su dung nua
        /// </summary>
        public int HIEULUC_THUTIEN { get; set; }
    }

    public class DINH_KHOAN_NO
    {
        public int TAI_KHOAN_NO { get; set; }
        public string TEN_TAI_KHOAN_NO { get; set; }
        public string GHI_CHU { get; set; }

        /// <summary>
        /// 1: Tien mat
        /// 2: Ngan hang
        /// NULL: khac
        /// </summary>
        public string HINH_THUC_THANH_TOAN { get; set; }

        public bool MAC_DINH { get; set; }

        /// <summary>
        /// 1: Dang duoc su dung
        /// 0: Khong con su dung nua
        /// </summary>
        public int HIEULUC_THUTIEN { get; set; }
    }

    public class CMS_DinhKhoan 
    {
        private CSQLServer sqlServer = new CSQLServer();

        #region Dinh khoan co

        public CMS_DinhKhoan()
        { }
        /// <summary>
        /// Desc: Lay danh sach dinh khoan co
        /// </summary>
        /// <returns></returns>
        public DataTable GetDinhKhoanCo()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STT,
                                    CONVERT(VARCHAR,TAI_KHOAN_CO) AS TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN
                            FROM DINH_KHOAN_CO";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        /// <summary>
        /// Desc: Lay danh sach dinh khoan co
        /// </summary>
        /// <returns></returns>
        public DataTable GetDinhKhoanCoCoHieuLuc()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STT,
                                    CONVERT(VARCHAR,TAI_KHOAN_CO) AS TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN,
                                     (CASE WHEN ISNULL(NHOM_TAI_KHOAN,0) = 0 THEN 0 ELSE 1 END) AS NHOM_TAI_KHOAN_BOOL
                            FROM DINH_KHOAN_CO WHERE HIEULUC_THUTIEN = 1 ";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        public List<DINH_KHOAN_CO> GetListDinhKhoanCoCoHieuLuc()
        {
            List<DINH_KHOAN_CO> List = new List<DINH_KHOAN_CO>();
            DataTable Table = GetDinhKhoanCoCoHieuLuc();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < Table.Rows.Count; i++)
                {
                    DINH_KHOAN_CO TKCo = ChuyenDoiDinhKhoanCo(Table.Rows[i]);
                    if (TKCo != null)
                    {
                        List.Add(TKCo);
                    }
                }
            }
            return List;
        }

        public DataTable GetDinhKhoanCoDoanhThuCoHieuLuc()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STT,
                                    CONVERT(VARCHAR,TAI_KHOAN_CO) AS TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN,
                                    (CASE WHEN ISNULL(NHOM_TAI_KHOAN,0) = 0 THEN 0 ELSE 1 END) AS NHOM_TAI_KHOAN_BOOL
                            FROM DINH_KHOAN_CO WHERE HIEULUC_THUTIEN = 1 AND NHOM_TAI_KHOAN = 1 ";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        /// <summary>
        /// Desc: Lay danh sach dinh khoan co
        /// </summary>
        /// <returns></returns>
        public DataTable GetDinhKhoanCoDoanhThuCauLong()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STT,
                                    CONVERT(VARCHAR,TAI_KHOAN_CO) AS TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN,
                                     (CASE WHEN ISNULL(NHOM_TAI_KHOAN,0) = 0 THEN 0 ELSE 1 END) AS NHOM_TAI_KHOAN_BOOL
                            FROM DINH_KHOAN_CO WHERE HIEULUC_THUTIEN = 1 AND NHOM_TAI_KHOAN=1 AND LOAI_TAI_KHOAN=N'Cầu lông'  ";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        public List<DINH_KHOAN_CO> GetListDinhKhoanCoDoanhThuCauLong()
        {
            List<DINH_KHOAN_CO> List = new List<DINH_KHOAN_CO>();
            DataTable Table = GetDinhKhoanCoDoanhThuCauLong();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < Table.Rows.Count; i++)
                {
                    DINH_KHOAN_CO TKCo = ChuyenDoiDinhKhoanCo(Table.Rows[i]);
                    if (TKCo != null)
                    {
                        List.Add(TKCo);
                    }
                }
            }
            return List;
        }

        public DINH_KHOAN_CO GetInfoDinhKhoanCoDoanhThuCauLongDauTien()
        {
            DINH_KHOAN_CO TKCo = new DINH_KHOAN_CO();
            DataTable Table = GetDinhKhoanCoDoanhThuCauLong();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                TKCo = ChuyenDoiDinhKhoanCo(Table.Rows[0]);
            }
            return TKCo;
        }


        public List<DINH_KHOAN_CO> GetListDinhKhoanCoDoanhThuCoHieuLuc()
        {
            List<DINH_KHOAN_CO> List = new List<DINH_KHOAN_CO>();
            DataTable Table = GetDinhKhoanCoDoanhThuCoHieuLuc();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < Table.Rows.Count; i++)
                {
                    DINH_KHOAN_CO TKCo= ChuyenDoiDinhKhoanCo(Table.Rows[i]);
                    if (TKCo !=null )
                    {
                        List.Add(TKCo);
                    }
                }
            }
            return List;
        }

        public DataTable GetDinhKhoanCoChiPhiCoHieuLuc()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STT,
                                    CONVERT(VARCHAR,TAI_KHOAN_CO) AS TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN,
                                    (CASE WHEN ISNULL(NHOM_TAI_KHOAN,0) = 0 THEN 0 ELSE 1 END) AS NHOM_TAI_KHOAN_BOOL
                            FROM DINH_KHOAN_CO WHERE HIEULUC_THUTIEN = 1 AND NHOM_TAI_KHOAN = 0 ";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        public List<DINH_KHOAN_CO> GetListDinhKhoanCoChiPhiCoHieuLuc()
        {
            List<DINH_KHOAN_CO> List = new List<DINH_KHOAN_CO>();
            DataTable Table = GetDinhKhoanCoChiPhiCoHieuLuc();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < Table.Rows.Count; i++)
                {
                    DINH_KHOAN_CO TKCo = ChuyenDoiDinhKhoanCo(Table.Rows[i]);
                    if (TKCo != null)
                    {
                        List.Add(TKCo);
                    }
                }
            }
            return List;
        }

        private DINH_KHOAN_CO ChuyenDoiDinhKhoanCo(DataRow Row)
        {
            DINH_KHOAN_CO result = new DINH_KHOAN_CO();
            try
            {
                result.TAI_KHOAN_CO = int.Parse(Row["TAI_KHOAN_CO"].ToString());
                result.TEN_TAI_KHOAN_CO = Row["TEN_TAI_KHOAN_CO"] == null ? string.Empty : Row["TEN_TAI_KHOAN_CO"].ToString();
                result.LOAI_TAI_KHOAN = Row["LOAI_TAI_KHOAN"] == null ? string.Empty : Row["LOAI_TAI_KHOAN"].ToString();
                result.NHOM_TAI_KHOAN = Row["TEN_TAI_KHOAN_CO"] == null ?false : bool.Parse(Row["NHOM_TAI_KHOAN"].ToString());
                result.HIEULUC_THUTIEN = Row["HIEULUC_THUTIEN_BOOL"] == DBNull.Value ? 0 : int.Parse(Row["HIEULUC_THUTIEN_BOOL"].ToString());
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

     
        /// <summary>
        /// Desc: Lay chi tiet dinh khoang co theo tai khoan co
        /// </summary>
        /// <param name="TAI_KHOAN_CO"></param>
        /// <returns></returns>
        public DINH_KHOAN_CO GetChiTietDinhKhoanCo(int TAI_KHOAN_CO)
        {
            DINH_KHOAN_CO dkCoEntity = null;
            try
            {
                string strSQL = @"SELECT TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN
                                FROM DINH_KHOAN_CO";
                strSQL += string.Format(" WHERE TAI_KHOAN_CO = {0} ", TAI_KHOAN_CO);

                DataTable dt = sqlServer.ReadDataQuery(strSQL);
                if (dt != null && dt.Rows.Count == 1)
                {
                    DataRow row = dt.Rows[0];

                    dkCoEntity = new DINH_KHOAN_CO();
                    dkCoEntity.TAI_KHOAN_CO = TAI_KHOAN_CO;
                    dkCoEntity.TEN_TAI_KHOAN_CO = row["TEN_TAI_KHOAN_CO"] == DBNull.Value ? string.Empty : row["TEN_TAI_KHOAN_CO"].ToString();
                    dkCoEntity.GHI_CHU = row["GHI_CHU"] == DBNull.Value ? string.Empty : row["GHI_CHU"].ToString();
                    dkCoEntity.LOAI_TAI_KHOAN = row["LOAI_TAI_KHOAN"] == DBNull.Value ? string.Empty : row["LOAI_TAI_KHOAN"].ToString();
                    dkCoEntity.NHOM_TAI_KHOAN = row["NHOM_TAI_KHOAN"] == DBNull.Value ? false : bool.Parse(row["NHOM_TAI_KHOAN"].ToString());

                    dkCoEntity.MAC_DINH = row["MAC_DINH"] == DBNull.Value ? false : bool.Parse(row["MAC_DINH"].ToString());

                    dkCoEntity.HIEULUC_THUTIEN = row["HIEULUC_THUTIEN"] == DBNull.Value ? 0 : int.Parse(row["HIEULUC_THUTIEN"].ToString());
                }
            }
            catch (System.Exception ex)
            {
                return null;
            }

            return dkCoEntity;
        }

        public DINH_KHOAN_CO GetChiTietDinhKhoanCoMacDinh()
        {
            DINH_KHOAN_CO dkCoEntity = null;
            try
            {
                string strSQL = @"SELECT TAI_KHOAN_CO,
                                    TEN_TAI_KHOAN_CO,
                                    GHI_CHU,
                                    LOAI_TAI_KHOAN,
                                    MAC_DINH,                               
                                    HIEULUC_THUTIEN,
                                    (CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL,
                                    NHOM_TAI_KHOAN
                                FROM DINH_KHOAN_CO
                                WHERE MAC_DINH = 1";

                DataTable dt = sqlServer.ReadDataQuery(strSQL);
                if (dt != null && dt.Rows.Count >0)
                {
                    DataRow row = dt.Rows[0];

                    dkCoEntity = new DINH_KHOAN_CO();
                    dkCoEntity.TAI_KHOAN_CO = int.Parse(row["TAI_KHOAN_CO"].ToString());
                    dkCoEntity.TEN_TAI_KHOAN_CO = row["TEN_TAI_KHOAN_CO"] == DBNull.Value ? string.Empty : row["TEN_TAI_KHOAN_CO"].ToString();
                    dkCoEntity.GHI_CHU = row["GHI_CHU"] == DBNull.Value ? string.Empty : row["GHI_CHU"].ToString();
                    dkCoEntity.LOAI_TAI_KHOAN = row["LOAI_TAI_KHOAN"] == DBNull.Value ? string.Empty : row["LOAI_TAI_KHOAN"].ToString();
                    dkCoEntity.NHOM_TAI_KHOAN = row["NHOM_TAI_KHOAN"] == DBNull.Value ? false : bool.Parse(row["NHOM_TAI_KHOAN"].ToString());

                    dkCoEntity.MAC_DINH = row["MAC_DINH"] == DBNull.Value ? false : bool.Parse(row["MAC_DINH"].ToString());

                    dkCoEntity.HIEULUC_THUTIEN = row["HIEULUC_THUTIEN"] == DBNull.Value ? 0 : int.Parse(row["HIEULUC_THUTIEN"].ToString());
                }
            }
            catch (System.Exception ex)
            {
                return null;
            }

            return dkCoEntity;
        }

        /// <summary>
        /// Author:Halm
        /// Date: 2012/10/14
        /// Desc: Kiem tra dinh khoan co da ton tai trong database hay chua
        /// </summary>
        /// <param name="TAI_KHOAN_CO"></param>
        /// <returns></returns>
        public bool CheckTonTaiDinhKhoanCo(int TAI_KHOAN_CO)
        {
            return GetChiTietDinhKhoanCo(TAI_KHOAN_CO) != null;
        }

        /// <summary>
        /// 
        /// 
        /// Desc: Insert mot dong dinh khoan co
        /// </summary>
        /// <param name="dinhKhoanCoEntity"></param>
        /// <returns></returns>
        public bool InsertDinhKhoanCo(DINH_KHOAN_CO dinhKhoanCoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"INSERT INTO [DINH_KHOAN_CO](
                                TAI_KHOAN_CO,
                                TEN_TAI_KHOAN_CO,
                                GHI_CHU,
                                LOAI_TAI_KHOAN,
                                MAC_DINH,
                                LOCKS,
                                HIEULUC_THUTIEN, NHOM_TAI_KHOAN)
                            VALUES (";
                strSQL += string.Format("{0}", dinhKhoanCoEntity.TAI_KHOAN_CO);
                strSQL += string.Format(",N'{0}'", dinhKhoanCoEntity.TEN_TAI_KHOAN_CO);
                strSQL += string.Format(",N'{0}'", dinhKhoanCoEntity.GHI_CHU);
                strSQL += string.Format(",'{0}'", dinhKhoanCoEntity.LOAI_TAI_KHOAN);

                strSQL += string.Format(",0");//Mac dinh
                strSQL += string.Format(",0");//LOCKS

                strSQL += string.Format(",{0}", dinhKhoanCoEntity.HIEULUC_THUTIEN);
                strSQL += string.Format(",'{0}'", dinhKhoanCoEntity.NHOM_TAI_KHOAN);
                strSQL += string.Format(")");

                result = sqlServer.Exec(strSQL) > 0;//1: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }

        /// <summary>
        /// 
        /// 
        /// Desc: Update gia tri mot dong dinh khoan co
        /// </summary>
        /// <param name="dinhKhoanCoEntity"></param>
        /// <returns></returns>
        public bool UpdateDinhKhoanCo(DINH_KHOAN_CO dinhKhoanCoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"UPDATE [DINH_KHOAN_CO] SET ";
                strSQL += string.Format("TEN_TAI_KHOAN_CO = N'{0}'", dinhKhoanCoEntity.TEN_TAI_KHOAN_CO);
                strSQL += string.Format(",GHI_CHU = N'{0}'", dinhKhoanCoEntity.GHI_CHU);
                strSQL += string.Format(",NHOM_TAI_KHOAN = N'{0}'", dinhKhoanCoEntity.NHOM_TAI_KHOAN);

                strSQL += string.Format(",HIEULUC_THUTIEN = {0}", dinhKhoanCoEntity.HIEULUC_THUTIEN);
                strSQL += string.Format(" WHERE TAI_KHOAN_CO = {0}", dinhKhoanCoEntity.TAI_KHOAN_CO);

                result = sqlServer.Exec(strSQL) > 0;//1: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }

        /// <summary>
        /// 
        /// 
        /// Desc: Update gia tri mot dong dinh khoan co
        /// </summary>
        /// <param name="dinhKhoanCoEntity"></param>
        /// <returns></returns>
        public bool DeleteDinhKhoanCo(DINH_KHOAN_CO dinhKhoanCoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"DELETE [DINH_KHOAN_CO] ";
                strSQL += string.Format(" WHERE TAI_KHOAN_CO = {0}", dinhKhoanCoEntity.TAI_KHOAN_CO);
                result = sqlServer.Exec(strSQL) > 0;//1: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }


        #endregion End of dinh khoan co

        #region Dinh khoan No

        /// <summary>
        /// 
        /// 
        /// Desc: Lay danh sach dinh khoan NO
        /// </summary>
        /// <returns></returns>
        public DataTable GetDinhKhoangNo()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STTNo
                                    ,CONVERT(VARCHAR,TAI_KHOAN_NO) AS TAI_KHOAN_NO
                                    ,TEN_TAI_KHOAN_NO
                                    ,HINH_THUC_THANH_TOAN                                    
                                    ,(CASE HINH_THUC_THANH_TOAN
                                        WHEN 1 THEN N'Tiền mặt' 
                                        WHEN 2 THEN N'Chuyển khoản'
                                        ELSE N'Khác' END ) AS HINH_THUC_THANH_TOAN_TEXT
                                    ,MAC_DINH
                                    ,HIEULUC_THUTIEN
                                    ,(CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL                                
                                FROM DINH_KHOAN_NO";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        /// <summary>

        /// Desc: Lay chi tiet dinh khoang NO theo tai khoan NO
        /// </summary>
        /// <param name="TAI_KHOAN_NO"></param>
        /// <returns></returns>
        public DataTable GetDinhKhoangNoCoHieuLuc()
        {
            DataTable dt = null;
            try
            {
                string strSQL = @"SELECT '' as STTNo
                                    ,CONVERT(VARCHAR,TAI_KHOAN_NO) AS TAI_KHOAN_NO
                                    ,TEN_TAI_KHOAN_NO
                                    ,HINH_THUC_THANH_TOAN                                    
                                    ,(CASE HINH_THUC_THANH_TOAN
                                        WHEN 1 THEN N'Tiền mặt' 
                                        WHEN 2 THEN N'Chuyển khoản'
                                        ELSE N'Khác' END ) AS HINH_THUC_THANH_TOAN_TEXT
                                    ,MAC_DINH
                                    ,HIEULUC_THUTIEN
                                    ,(CASE WHEN ISNULL(HIEULUC_THUTIEN,0) = 0 THEN 0 ELSE 1 END) AS HIEULUC_THUTIEN_BOOL                                
                                FROM DINH_KHOAN_NO  WHERE HIEULUC_THUTIEN = 1 ";

                dt = sqlServer.ReadDataQuery(strSQL);
            }
            catch (System.Exception ex)
            {
            }

            return dt;
        }

        public List<DINH_KHOAN_NO> GetListDinhKhoanNoCoHieuLuc()
        {
            List<DINH_KHOAN_NO> List = new List<DINH_KHOAN_NO>();
            DataTable Table = GetDinhKhoangNoCoHieuLuc();
            if (Table == null || Table.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < Table.Rows.Count; i++)
                {
                    DINH_KHOAN_NO TKNo = ChuyenDoiDinhKhoanNo(Table.Rows[i]);
                    if (TKNo != null)
                    {
                        List.Add(TKNo);
                    }
                }
            }
            return List;
        }

        private DINH_KHOAN_NO ChuyenDoiDinhKhoanNo(DataRow Row)
        {
            DINH_KHOAN_NO result = new DINH_KHOAN_NO();
            try
            {
                result.TAI_KHOAN_NO = int.Parse(Row["TAI_KHOAN_NO"].ToString());
                result.TEN_TAI_KHOAN_NO = Row["TEN_TAI_KHOAN_NO"] == null ? string.Empty : Row["TEN_TAI_KHOAN_NO"].ToString();
                result.HINH_THUC_THANH_TOAN = Row["HINH_THUC_THANH_TOAN"] == null ? string.Empty : Row["HINH_THUC_THANH_TOAN"].ToString();
                result.HIEULUC_THUTIEN = Row["HIEULUC_THUTIEN_BOOL"] == DBNull.Value ? 0 : int.Parse(Row["HIEULUC_THUTIEN_BOOL"].ToString());
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 
        ///
        /// Desc: Lay chi tiet dinh khoang NO theo tai khoan NO
        /// </summary>
        /// <param name="TAI_KHOAN_NO"></param>
        /// <returns></returns>
        public DINH_KHOAN_NO GetChiTietDinhKhoanNo(int TAI_KHOAN_NO)
        {
            DINH_KHOAN_NO dkNoEntity = null;
            try
            {
                string strSQL = @"SELECT TAI_KHOAN_NO
                                    ,TEN_TAI_KHOAN_NO
                                    ,GHI_CHU
                                    ,ISNULL(HINH_THUC_THANH_TOAN,'') HINH_THUC_THANH_TOAN
                                    ,(CASE HINH_THUC_THANH_TOAN
                                        WHEN 1 THEN N'Tiền mặt' 
                                        WHEN 2 THEN N'Chuyển khoản'
                                        ELSE N'Khác' END ) AS HINH_THUC_THANH_TOAN_TEXT
                                    ,MAC_DINH
                                    ,HIEULUC_THUTIEN
                                FROM DINH_KHOAN_NO";
                strSQL += string.Format(" WHERE TAI_KHOAN_NO = {0} ", TAI_KHOAN_NO);

                DataTable dt = sqlServer.ReadDataQuery(strSQL);
                if (dt != null && dt.Rows.Count == 1)
                {
                    DataRow row = dt.Rows[0];

                    dkNoEntity = new DINH_KHOAN_NO();
                    dkNoEntity.TAI_KHOAN_NO = TAI_KHOAN_NO;
                    dkNoEntity.TEN_TAI_KHOAN_NO = row["TEN_TAI_KHOAN_NO"] == DBNull.Value ? string.Empty : row["TEN_TAI_KHOAN_NO"].ToString();
                    dkNoEntity.GHI_CHU = row["GHI_CHU"] == DBNull.Value ? string.Empty : row["GHI_CHU"].ToString();
                    dkNoEntity.HINH_THUC_THANH_TOAN = row["HINH_THUC_THANH_TOAN"] == DBNull.Value ? string.Empty : row["HINH_THUC_THANH_TOAN"].ToString();
                    dkNoEntity.MAC_DINH = row["MAC_DINH"] == DBNull.Value ? false : bool.Parse(row["MAC_DINH"].ToString());

                    dkNoEntity.HIEULUC_THUTIEN = row["HIEULUC_THUTIEN"] == DBNull.Value ? 0 : int.Parse(row["HIEULUC_THUTIEN"].ToString());
                }
            }
            catch (System.Exception ex)
            {
                return null;
            }

            return dkNoEntity;
        }

        public DINH_KHOAN_NO GetChiTietDinhKhoanNoMacDinh()
        {
            DINH_KHOAN_NO dkNoEntity = null;
            try
            {
                string strSQL = @"SELECT TAI_KHOAN_NO
                                    ,TEN_TAI_KHOAN_NO
                                    ,GHI_CHU
                                    ,ISNULL(HINH_THUC_THANH_TOAN,'') HINH_THUC_THANH_TOAN
                                    ,(CASE HINH_THUC_THANH_TOAN
                                        WHEN 1 THEN N'Tiền mặt' 
                                        WHEN 2 THEN N'Chuyển khoản'
                                        ELSE N'Khác' END ) AS HINH_THUC_THANH_TOAN_TEXT
                                    ,MAC_DINH
                                    ,HIEULUC_THUTIEN
                                FROM DINH_KHOAN_NO
                                WHERE MAC_DINH = 1 ";

                DataTable dt = sqlServer.ReadDataQuery(strSQL);
                if (dt != null && dt.Rows.Count >0)
                {
                    DataRow row = dt.Rows[0];

                    dkNoEntity = new DINH_KHOAN_NO();
                    dkNoEntity.TAI_KHOAN_NO = int.Parse(row["TAI_KHOAN_NO"].ToString());
                    dkNoEntity.TEN_TAI_KHOAN_NO = row["TEN_TAI_KHOAN_NO"] == DBNull.Value ? string.Empty : row["TEN_TAI_KHOAN_NO"].ToString();
                    dkNoEntity.GHI_CHU = row["GHI_CHU"] == DBNull.Value ? string.Empty : row["GHI_CHU"].ToString();
                    dkNoEntity.HINH_THUC_THANH_TOAN = row["HINH_THUC_THANH_TOAN"] == DBNull.Value ? string.Empty : row["HINH_THUC_THANH_TOAN"].ToString();
                    dkNoEntity.MAC_DINH = row["MAC_DINH"] == DBNull.Value ? false : bool.Parse(row["MAC_DINH"].ToString());

                    dkNoEntity.HIEULUC_THUTIEN = row["HIEULUC_THUTIEN"] == DBNull.Value ? 0 : int.Parse(row["HIEULUC_THUTIEN"].ToString());
                }
            }
            catch (System.Exception ex)
            {
                return null;
            }

            return dkNoEntity;
        }

        /// <summary>
        /// 
        /// 
        /// Desc: Kiem tra dinh khoan NO da ton tai trong database hay chua
        /// </summary>
        /// <param name="TAI_KHOAN_CO"></param>
        /// <returns></returns>
        public bool CheckTonTaiDinhKhoanNo(int TAI_KHOAN_NO)
        {
            return GetChiTietDinhKhoanNo(TAI_KHOAN_NO) != null;
        }

        /// <summary>
        /// 
        ///
        /// Desc: Insert mot dong dinh khoan No
        /// </summary>
        /// <param name="dinhKhoanNoEntity"></param>
        /// <returns></returns>
        public bool InsertDinhKhoanNo(DINH_KHOAN_NO dinhKhoanNoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"INSERT INTO [DINH_KHOAN_NO](
                                TAI_KHOAN_NO
                                ,TEN_TAI_KHOAN_NO
                                ,GHI_CHU
                                ,HINH_THUC_THANH_TOAN
                                ,MAC_DINH
                                ,LOCKS
                                ,HIEULUC_THUTIEN)
                            VALUES (";
                strSQL += string.Format("{0}", dinhKhoanNoEntity.TAI_KHOAN_NO);
                strSQL += string.Format(",N'{0}'", dinhKhoanNoEntity.TEN_TAI_KHOAN_NO);
                strSQL += string.Format(",N'{0}'", dinhKhoanNoEntity.GHI_CHU);
                strSQL += string.Format(",'{0}'", dinhKhoanNoEntity.HINH_THUC_THANH_TOAN);
                strSQL += string.Format(",0");//Mac dinh
                strSQL += string.Format(",0");//LOCKS
                strSQL += string.Format(",{0}", dinhKhoanNoEntity.HIEULUC_THUTIEN);
                strSQL += string.Format(")");

                result = sqlServer.Exec(strSQL) > 0;//0: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }

        /// <summary>
        /// 
        /// 
        /// Desc: Update gia tri mot dong dinh khoan NO
        /// </summary>
        /// <param name="dinhKhoanNoEntity"></param>
        /// <returns></returns>
        public bool UpdateDinhKhoanNo(DINH_KHOAN_NO dinhKhoanNoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"UPDATE [DINH_KHOAN_NO] SET ";
                strSQL += string.Format("TEN_TAI_KHOAN_NO = N'{0}'", dinhKhoanNoEntity.TEN_TAI_KHOAN_NO);
                strSQL += string.Format(",GHI_CHU = N'{0}'", dinhKhoanNoEntity.GHI_CHU);
                strSQL += string.Format(",HIEULUC_THUTIEN = {0}", dinhKhoanNoEntity.HIEULUC_THUTIEN);
                strSQL += string.Format(" WHERE TAI_KHOAN_NO = {0}", dinhKhoanNoEntity.TAI_KHOAN_NO);

                result = sqlServer.Exec(strSQL) > 0;//0: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }

        public bool DeleteDinhKhoanNo(DINH_KHOAN_NO dinhKhoanNoEntity)
        {
            bool result = false;

            try
            {
                string strSQL = string.Empty;
                strSQL = @"DELETE [DINH_KHOAN_NO] ";
                strSQL += string.Format(" WHERE TAI_KHOAN_NO = {0}", dinhKhoanNoEntity.TAI_KHOAN_NO);

                result = sqlServer.Exec(strSQL) > 0;//0: Thank cong; -1: Khong thanh cong
            }
            catch (Exception ex)
            { }
            return result;
        }


        #endregion End of dinh khoan No
    }
}
